package com.hoyo.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.hoyo.domain.Catalog;
import com.hoyo.domain.CatalogCollect;
import com.hoyo.domain.CatalogReport;

@Mapper
public interface CatalogMapper {
	
	@Insert("insert into user_catalog values(0,#{userId},#{catalogName},#{catalogPoster},#{introduction},#{channelId},#{collectAmount},#{createTime}"
			+ ", #{isChecked}, #{isShowed})")
	int save(Catalog catalog) throws Exception;
	
	@Select("select uc.id, uc.catalog_name, uc.catalog_poster,ui.user_id, ui.nickname "
			+ "from user_catalog uc left join user_info ui on ui.user_id = uc.user_id "
			+ "where uc.is_checked=#{0} order by uc.create_time desc limit #{1},#{2}")
	List<Catalog> findCatalogsByChecked(boolean isChecked, int offset, int count) throws Exception;
	
	@Select("select * from user_catalog uc left join user u on u.id=uc.user_id and (u.phone=#{account} or u.email=#{account}) where uc.is_showed=true")
	List<Catalog> findCatalogsByAccount(String account) throws Exception;
	
	@Update("update user_catalog set is_checked=#{1}, is_showed=#{2} where id=#{0}")
	void updateCatalogIsCheckedAndIsShowedById(int id, boolean isChecked, boolean isShowed) throws Exception;
	
	@Select("select * from user_catalog where is_showed=#{isShowed}")
	List<Catalog> findCatalogsByShowed(boolean isShowed) throws Exception;
	
	/**
	 * 按上传时间降序获取目录信息
	 * @param channelId
	 * @param limit
	 * @return
	 * @throws Exception
	 */
	@Select("select uc.id, uc.catalog_poster, uc.catalog_name, sum(mci.play_amount) play_amount, sum(mci.comment_amount+mci.danmu_amount) amount "
			+ "from user_catalog uc "
			+ "left join movie mv "
			+ "on mv.catalog_id=uc.id "
			+ "left join movie_count_info mci "
			+ "on mci.movie_id=mv.id "
			+ "left join channel c "
			+ "on c.id=uc.channel_id "
			+ "where c.id=#{0} and uc.is_showed=true "
			+ "group by catalog_id "
			+ "order by uc.create_time desc "
			+ "limit #{1}")
	List<Catalog> findCatalogsByChannelIdOrderByDateDescLimitBy(int channelId, int limit)throws Exception ;
	
	/**
	 * 按播放量降序排行
	 * @param channelId
	 * @param limit
	 * @return
	 * @throws Exception
	 */
	@Select("select uc.id, uc.catalog_poster, uc.catalog_name, sum(mci.play_amount) play_amount "
			+ "from user_catalog uc "
			+ "left join movie mv "
			+ "on mv.catalog_id=uc.id "
			+ "left join movie_count_info mci "
			+ "on mci.movie_id=mv.id "
			+ "left join channel c "
			+ "on c.id=uc.channel_id "
			+ "where c.id=#{0} and uc.is_showed=true "
			+ "group by catalog_id "
			+ "order by play_amount desc "
			+ "limit #{1}")
	List<Catalog> findCatalogsOrderByPlayAmountDescLimitBy(int channelId, int limit)throws Exception;
	
	/**
	 * 通过id获取视频目录信息
	 * @param catalogId
	 * @return
	 * @throws Exception
	 */
	@Select("select uc.id, uc.catalog_name,uc.catalog_poster, uc.create_time, uc.introduction, "
			+ "ui.icon, ui.nickname, ui.exp, ui.description, "
			+ "c.id channel_id, c.channel_name, "
			+ "(select count(*) collect_amount from catalog_collect where catalog_id=#{0}) collect_amount, "
			+ "sum(mci.play_amount) play_amount, sum(mci.danmu_amount) danmu_amount, sum(mci.coin_amount) coin_amount "
			+ "from user_catalog uc left join user_info ui on ui.user_id=uc.user_id left join channel c on c.id=uc.channel_id "
			+ "left join movie mv on mv.catalog_id=uc.id left join movie_count_info mci on mci.movie_id=mv.id where uc.id=#{0}")
	Catalog findById(int catalogId)throws Exception;
	
	/*
	 * 查询三天内的投稿信息
	 */
	@Select("select uc.id, uc.catalog_name, uc.create_time,c.channel_name,uc.is_checked from user_catalog uc left join user u on u.id=uc.user_id  left join channel c on c.id=uc.channel_id where u.id=#{0} AND uc.create_time >DATE_SUB(NOW(),INTERVAL 3 DAY)")
	List<Catalog> findCatalogsByAccountId(int accountId) throws Exception;
	
	/**
	 * 查询审核或未审核的目录
	 * @param isChecked
	 * @param offset
	 * @param count
	 * @return
	 * @throws Exception
	 */
	@Select("select uc.*, c.channel_name, ui.nickname, (select count(*) from user_catalog where is_checked=#{0}) total "
			+ "from user_catalog uc left join `channel` c on c.id=uc.channel_id "
			+ "left join user_info ui on ui.user_id=uc.user_id "
			+ "where is_checked =#{0} order by uc.create_time limit #{1},#{2}")
	List<Catalog> findCatalogsByIsCheckedOrderByCreateTimeLimitBy(boolean isChecked, int offset, int count)throws Exception;

	/**
	 * 通过创建时间升序获取目录信息
	 * @param offset
	 * @param count
	 * @return
	 * @throws Exception
	 */
	@Select("select uc.*, c.channel_name, ui.nickname, (select count(*) from user_catalog) total, collect_info.collect_amount, "
			+ "sum(mci.play_amount) play_amount, sum(mci.danmu_amount) danmu_amount, sum(mci.comment_amount) comment_amount "
			+ "from user_catalog uc left join `channel` c on c.id=uc.channel_id "
			+ "left join user_info ui on ui.user_id=uc.user_id "
			+ "left join movie mv on mv.catalog_id=uc.id left join movie_count_info mci on mci.movie_id=mv.id "
			+ "left join (select catalog_id, count(*) collect_amount from catalog_collect group by catalog_id) collect_info "
			+ "on collect_info.catalog_id = uc.id "
			+ "group by uc.id order by uc.create_time limit #{0},#{1}")
	List<Catalog> findCatalogsOrderByCreateTimeLimitBy(int offset, int count)throws Exception;
	
	/**
	 * 通过频道id，按时间升序获取目录信息
	 * @param channelId
	 * @param offset
	 * @param count
	 * @return
	 * @throws Exception
	 */
	@Select("select uc.*, c.channel_name, ui.nickname, (select count(*) from user_catalog) total, collect_info.collect_amount, "
			+ "sum(mci.play_amount) play_amount, sum(mci.danmu_amount) danmu_amount, sum(mci.comment_amount) comment_amount "
			+ "from user_catalog uc left join `channel` c on c.id=uc.channel_id "
			+ "left join user_info ui on ui.user_id=uc.user_id "
			+ "left join movie mv on mv.catalog_id=uc.id left join movie_count_info mci on mci.movie_id=mv.id "
			+ "left join (select catalog_id, count(*) collect_amount from catalog_collect group by catalog_id) collect_info "
			+ "on collect_info.catalog_id = uc.id "
			+ "where c.id=#{0} "
			+ "group by uc.id order by uc.create_time limit #{1},#{2}")
	List<Catalog> findCatalogsByChannelIdOrderByCreateTimeLimitBy(int channelId, int offset, int count)throws Exception;

	@Update("delete from user_catalog where id=#{0}")
	void deleteById(int id) throws Exception;
	
	@Update("update user_catalog set is_showed = #{1} where id=#{0}")
	void updateCatalogByIsShowedAndId(int id, boolean isShowed) throws Exception;
	
	/**
	 * 按关键字查询目录信息，有效检测字段目录名、作者、频道
	 * @param keyword
	 * @param offset
	 * @param count
	 * @return
	 * @throws Exception
	 */
	@Select("select uc.*, c.channel_name, ui.nickname, (select count(*) from user_catalog) total, collect_info.collect_amount, "
			+ "sum(mci.play_amount) play_amount, sum(mci.danmu_amount) danmu_amount, sum(mci.comment_amount) comment_amount "
			+ "from user_catalog uc left join `channel` c on c.id=uc.channel_id "
			+ "left join user_info ui on ui.user_id=uc.user_id "
			+ "left join movie mv on mv.catalog_id=uc.id left join movie_count_info mci on mci.movie_id=mv.id "
			+ "left join (select catalog_id, count(*) collect_amount from catalog_collect group by catalog_id) collect_info "
			+ "on collect_info.catalog_id = uc.id "
			+ "where c.channel_name like #{keyword} or uc.catalog_name like #{keyword} or ui.nickname like #{keyword} "
			+ "group by uc.id order by uc.create_time limit #{1},#{2}")
	List<Catalog> findCatalogsByKeywordOrderByCreateTimeLimitBy(@Param("keyword")String keyword, int offset, int count)throws Exception;
	
	

	
	/*
	 * 根据频道类型、日期降序查询前x
	 */
	@Select("SELECT uc.id, catalog_name, catalog_poster,SUM(mci.play_amount) play_amount, SUM(mci.comment_amount+mci.danmu_amount) amount, (select count(*) from user_catalog WHERE channel_id=#{0} and is_checked=1 and is_showed=1) total FROM user_catalog uc LEFT JOIN movie mv ON mv.catalog_id=uc.id LEFT JOIN movie_count_info mci ON mci.movie_id=mv.id WHERE uc.channel_id=#{0} and uc.is_checked=1 and uc.is_showed=1 GROUP BY catalog_id ORDER BY uc.create_time DESC LIMIT #{1},#{2}")
	List<Catalog> findCatalogsByChannelsTypeAndSortByDate(int channelId, int offset, int count) throws Exception;
	
	
	/*
	 * 根据频道类型、播放量降序查询前x
	 */
	@Select("SELECT uc.id, catalog_name, catalog_poster,SUM(mci.play_amount) play_amount, SUM(mci.comment_amount+mci.danmu_amount) amount, (select count(*) from user_catalog WHERE channel_id=#{0} and is_checked=1 and is_showed=1) total FROM user_catalog uc LEFT JOIN movie mv ON mv.catalog_id=uc.id LEFT JOIN movie_count_info mci ON mci.movie_id=mv.id WHERE uc.channel_id=#{0} and uc.is_checked=1 and uc.is_showed=1 GROUP BY catalog_id ORDER BY play_amount DESC LIMIT #{1},#{2}")
	List<Catalog> findCatalogsByChannelsTypeAndSortByPlayAmount(int channelId, int offset, int count) throws Exception;
	
	@Insert("insert into catalog_collect(user_id,catalog_id) values(#{userId},#{catalogId})")
	void shoucangCatalogsById(CatalogCollect cc) throws Exception;
	
	
	@Delete("delete from catalog_collect where user_id=#{1} and catalog_id=#{0}")
	void cancelshoucangCatalogsById(int catalogId,int userId) throws Exception;
	
	@Select("select uc.*  from catalog_collect cc left join user_catalog uc on uc.id=cc.catalog_id where cc.user_id=#{0}")
	List<Catalog> findUserLikeCatalogByUserId(int userId) throws Exception;
	
	@Select("select *  from catalog_collect where catalog_id=#{0} and user_id=#{1}")
	List<CatalogCollect>  findUserisLikedCatalogByUserId(int catalogId,int userId) throws Exception;
	
	@Select("select cr.*, uc.catalog_name, uc.catalog_poster, ui.nickname, c.channel_name, "
			+ "(select count(*) from catalog_report) total "
			+ "from catalog_report cr "
			+ "left join user_info ui on ui.user_id=cr.user_id "
			+ "left join user_catalog uc on uc.id=cr.catalog_id left join channel c "
			+ "on c.id=uc.channel_id where cr.is_solved=0 limit #{0},#{1}")
	List<CatalogReport> findCatalogReportsLimitBy(int offset, int count) throws Exception;
	
	@Update("update catalog_report set is_solved=#{1}, is_really=#{2} where id=#{0}")
	void updateCatalogReportByIdAndIsSolvedAndIsReally(int id, boolean isSolved, boolean isReally);
	
	@Insert("insert into catalog_report(user_id,catalog_id,type,description) values(#{userId},#{catalogId},#{type},#{description})")
	void saveCatalogReport(CatalogReport cr) throws Exception;
}



